package com.ruoyi.quartz.controller;

import cn.hutool.http.Header;
import cn.hutool.http.HttpRequest;
import cn.hutool.http.HttpResponse;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.ruoyi.quartz.util.CellUtil;
import com.ruoyi.system.domain.DTO.WebsiteDTO;
import com.ruoyi.system.domain.vo.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import javax.activation.DataHandler;
import javax.activation.DataSource;
import javax.mail.*;
import javax.mail.internet.*;
import javax.mail.util.ByteArrayDataSource;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @author xuzheng
 * @date 2021年10月11日 11:03
 */
public class NewTheadController extends Thread {
    private String ectimgcaptcha;

    public NewTheadController(String ectimgcaptcha) {
        this.ectimgcaptcha = ectimgcaptcha;

    }


    @Override
    public void run() {
        try {
            getWebsiteDataList();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //官网登陆获取参数
    public String getWebsiteLogin() {
        //数据转换
        Map<String, Object> paramMap = new HashMap<>();
        paramMap.put("username", "前程环球海空通运");
        paramMap.put("password", "AuudPtMtl5r2OoNawoF1ZZIwdv3kE6FhOeYVcIDI/yTerVK5Q0H3jA3K7Xc0HEvAQb41NBlNZOLAtl0do/6WpoeuS1Gdu2yQgI3I3j8PBUFJ8WIthQAhdPxgxgu4qOJ8TJzx3sP3I8zvj0AMY/ObOO3j9Z3jbMTJKtqQAgtgG7nROJ2TrPyMuINv/cyOzzBZUj0VfKu41tduU3zlHCIhmgqG4IF1fRDxDwoJKhaUD2nUSs6nIOITxfwMeMWs6rXwpFDNYUVCwU//yOZaccCAxg3yVCz7DIdxt4C+nwNBE1BxUaFGRUfcAmXDAR+isPrW1uBYlKwhqlQyV3pOasJssg==");
        String url = "https://synconhub.coscoshipping.com/api/admin/user/login";
        HttpResponse httpResponse = getResponse(ectimgcaptcha);
        if (httpResponse.getStatus() == 200) {
            if (httpResponse.headers().get("X-Auth-Token") != null) {
                return httpResponse.headers().get("X-Auth-Token").get(0);
            }
        }
        return null;
    }

    //登录请求
    public HttpResponse getResponse(String ectimgcaptcha) {
        //数据转换
        Map<String, Object> paramMap = new HashMap<>();
        paramMap.put("username", "前程环球海空通运");
        paramMap.put("password", "AuudPtMtl5r2OoNawoF1ZZIwdv3kE6FhOeYVcIDI/yTerVK5Q0H3jA3K7Xc0HEvAQb41NBlNZOLAtl0do/6WpoeuS1Gdu2yQgI3I3j8PBUFJ8WIthQAhdPxgxgu4qOJ8TJzx3sP3I8zvj0AMY/ObOO3j9Z3jbMTJKtqQAgtgG7nROJ2TrPyMuINv/cyOzzBZUj0VfKu41tduU3zlHCIhmgqG4IF1fRDxDwoJKhaUD2nUSs6nIOITxfwMeMWs6rXwpFDNYUVCwU//yOZaccCAxg3yVCz7DIdxt4C+nwNBE1BxUaFGRUfcAmXDAR+isPrW1uBYlKwhqlQyV3pOasJssg==");
        String url = "https://synconhub.coscoshipping.com/api/admin/user/login";
        HttpResponse httpResponse = HttpRequest.post(url)
                .header(Header.ACCEPT, "application/json, text/plain, */*")
                .header(Header.ACCEPT_ENCODING, "gzip, deflate, br")
                .header(Header.ACCEPT_LANGUAGE, "zh-CN,zh;q=0.9")
                .header(Header.CONNECTION, "keep-alive")
                .header(Header.CONTENT_LENGTH, "456")
                .header(Header.HOST, "synconhub.coscoshipping.com")
                .header(Header.COOKIE, "HMF_CI=8dfc3ba663d39cf439c5378b2316d337ee05bc0c2df29c12ccc156a14570e1c791; CSH_DF=1ClHD9LpVa84OwZAh5MOUdlqMdXnkb2KAofAIwHYnfU3s2rZMw+UYawPG5UjcjoVQ3; django_language=zh-cn; sessionid=sh070idot52xqiapx8i0xsztll1kxo8i; cookiePolicy=Accepted; cookiePreference=Accepted; Hm_lvt_d16e4336470e6b4d88aa16ff927d7daf=1633911952; ECTIMGCAPTCHA=" + ectimgcaptcha + "; Hm_lpvt_d16e4336470e6b4d88aa16ff927d7daf=1633912231")
                .header(Header.CONTENT_TYPE, "application/x-www-form-urlencoded")
                .header("ECTIMGCAPTCHA", ectimgcaptcha)//验证码
                .header(Header.REFERER, "https://synconhub.coscoshipping.com/")
                .form(paramMap).execute();
        return httpResponse;
    }

    /**
     * 查询官网商品
     */
    public void getWebsiteDataList() throws Exception {
        //获取token参数
        String token = getWebsiteLogin();
        if (token == null || "".equals(token) || token.trim().length() == 0) {
            throw new RuntimeException("未获取到有效的token参数");
        }

        System.out.println("查询官网商品");

        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        List<WebsiteCityVo> websiteCityVoList = new ArrayList<>();
        String str = "https://synconhub.coscoshipping.com/product?tradeGroupCode=all&startDate=" + simpleDateFormat.format(new Date()) + "%2000%3A00&channelCode=GENERAL&cargoCategory=GENERAL&prodType=I";
        //青岛
        WebsiteCityVo websiteCityVo1 = new WebsiteCityVo();
        websiteCityVo1.setPorCityId("738872886232847");
        websiteCityVo1.setPorCityName("青岛-Qingdao");
        websiteCityVo1.setReffer(str + "&porCityName=%E9%9D%92%E5%B2%9B-Qingdao&porCityId=738872886232847&page=1");
        websiteCityVoList.add(websiteCityVo1);
        //上海
        WebsiteCityVo websiteCityVo2 = new WebsiteCityVo();
        websiteCityVo2.setPorCityId("738872886232873");
        websiteCityVo2.setPorCityName("上海-Shanghai");
        websiteCityVo2.setReffer(str + "&porCityName=%E4%B8%8A%E6%B5%B7-Shanghai&porCityId=738872886232873&page=1");
        websiteCityVoList.add(websiteCityVo2);
        //宁波
        WebsiteCityVo websiteCityVo3 = new WebsiteCityVo();
        websiteCityVo3.setPorCityId("738872886232879");
        websiteCityVo3.setPorCityName("宁波-Ningbo");
        websiteCityVo3.setReffer(str + "&porCityName=%E5%AE%81%E6%B3%A2-Ningbo&porCityId=738872886232879&page=1");
        websiteCityVoList.add(websiteCityVo3);
        //厦门
        WebsiteCityVo websiteCityVo4 = new WebsiteCityVo();
        websiteCityVo4.setPorCityId("738872886232719");
        websiteCityVo4.setPorCityName("厦门-Xiamen");
        websiteCityVo4.setReffer(str + "&porCityName=%E5%8E%A6%E9%97%A8-Xiamen&porCityId=738872886232719&page=1");
        websiteCityVoList.add(websiteCityVo4);
        //大连
        WebsiteCityVo websiteCityVo5 = new WebsiteCityVo();
        websiteCityVo5.setPorCityId("738872886233310");
        websiteCityVo5.setPorCityName("大连-Dalian");
        websiteCityVo5.setReffer(str + "&porCityName=%E5%A4%A7%E8%BF%9E-Dalian&porCityId=738872886233310&page=1");
        websiteCityVoList.add(websiteCityVo5);
        //深圳
        WebsiteCityVo websiteCityVo6 = new WebsiteCityVo();
        websiteCityVo6.setPorCityId("738872886233057");
        websiteCityVo6.setPorCityName("深圳-Shenzhen");
        websiteCityVo6.setReffer(str + "&porCityName=%E6%B7%B1%E5%9C%B3-Shenzhen&porCityId=738872886233057&page=1");
        websiteCityVoList.add(websiteCityVo6);
        //广州
        WebsiteCityVo websiteCityVo7 = new WebsiteCityVo();
        websiteCityVo7.setPorCityId("738872886233096");
        websiteCityVo7.setPorCityName(",广州,广东,中国-Zhoutouzui");
        websiteCityVo7.setReffer(str + "&porCityName=%2C%E5%B9%BF%E5%B7%9E%2C%E5%B9%BF%E4%B8%9C%2C%E4%B8%AD%E5%9B%BD-Zhoutouzui&porCityId=738872886233096&page=1");
        websiteCityVoList.add(websiteCityVo7);
        //天津
        WebsiteCityVo websiteCityVo8 = new WebsiteCityVo();
        websiteCityVo8.setPorCityId("738872886233294");
        websiteCityVo8.setPorCityName("天津-Tianjin");
        websiteCityVo8.setReffer(str + "&porCityName=%E5%A4%A9%E6%B4%A5-Tianjin&porCityId=738872886233294&page=1");
        websiteCityVoList.add(websiteCityVo8);

        List<WebsiteDTO> websiteDTOS = new ArrayList<>();
//        WebsiteDTO website1 = new WebsiteDTO();
//        website1.setTypeName("即刻订舱产品");
//        website1.setChannelCode("GENERAL");
//        website1.setCargoCategory("GENERAL");
//        website1.setProdType("I");
//        websiteDTOS.add(website1);

        WebsiteDTO website2 = new WebsiteDTO();
        website2.setTypeName("预定舱产品");
        website2.setChannelCode("GENERAL");
        website2.setCargoCategory("GENERAL");
        website2.setProdType("P");
        websiteDTOS.add(website2);
//
//        WebsiteDTO website3 = new WebsiteDTO();
//        website3.setTypeName("冷藏冷箱");
//        website3.setChannelCode("REEFER");
//        website3.setCargoCategory("REEFER");
//        website3.setProdType("I");
//        websiteDTOS.add(website3);


        //获取数据
        List<WebsiteVo> websiteVoList = new ArrayList<>();
        try {
            //循环不同产品
            for (WebsiteDTO websiteDTO : websiteDTOS) {
                //条数
                websiteDTO.setSize(50);
                //开航时间
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                websiteDTO.setStartDate(sdf.format(new Date()) + " 00:00");

                //循环不同城市
                for (WebsiteCityVo forWebsiteCityVo : websiteCityVoList) {
                    System.out.println("PorCityName====:" + forWebsiteCityVo.getPorCityName());
                    //赋值出发地
                    websiteDTO.setPorCityId(forWebsiteCityVo.getPorCityId());
                    websiteDTO.setPorCityName(forWebsiteCityVo.getPorCityName());

                    int pageNum = 10;
                    //循环分页
                    for (int m = 1; m <= pageNum; m++) {
                        Thread.sleep(500);
                        websiteDTO.setPage(m);
                        //数据转换
                        cn.hutool.json.JSONObject json = new cn.hutool.json.JSONObject();
                        json.put("startDate", websiteDTO.getStartDate());
                        json.put("porCityId", websiteDTO.getPorCityId());
                        json.put("porCityName", websiteDTO.getPorCityName());
                        json.put("cargoCategory", websiteDTO.getCargoCategory());
                        json.put("channelCode", websiteDTO.getChannelCode());
                        json.put("prodType", websiteDTO.getProdType());
                        json.put("page", websiteDTO.getPage());
                        json.put("size", websiteDTO.getSize());
                        String url = null;
                        if ("即刻订舱产品".equals(websiteDTO.getTypeName())) {
                            url = "https://synconhub.coscoshipping.com/api/product/client/sailing/product/list";
                        } else if ("预定舱产品".equals(websiteDTO.getTypeName())) {
                            url = "https://synconhub.coscoshipping.com/api/product/client/prebooking/group/list";
                        } else if ("冷藏冷箱".equals(websiteDTO.getTypeName())) {
                            url = "https://synconhub.coscoshipping.com/api/product/client/sailing/product/list";
                        }
                        String result = HttpRequest.post(url)
                                .header(Header.ACCEPT, "application/json, text/plain, */*")
                                .header(Header.ACCEPT_ENCODING, "gzip, deflate, br")
                                .header(Header.ACCEPT_LANGUAGE, "zh-CN,zh;q=0.9")
                                .header(Header.CONNECTION, "keep-alive")
                                .header(Header.CONTENT_LENGTH, "177")
                                .header(Header.HOST, "synconhub.coscoshipping.com")
                                .header(Header.COOKIE, "HMF_CI=cba99a261ef08e482821fb0dbc1b26396c8fb4932e552dcd50e1bdabab68cf4d5f; CSH_DF=1ClHD9LpVa84OwZAh5MOUdlqMdXnkb2KAofAIwHYnfU3s2rZMw+UYawPG5UjcjoVQ3; django_language=zh-cn; sessionid=u2wubta7apb2xd08exn71whiigg7l0mh; cookiePolicy=Accepted; cookiePreference=Accepted; Hm_lvt_d16e4336470e6b4d88aa16ff927d7daf=1633653041; token=" + token + "; Hm_lpvt_d16e4336470e6b4d88aa16ff927d7daf=1633654989")
                                .header("X-Auth-Token", token)
                                .header(Header.CONTENT_TYPE, "application/json;charset=UTF-8")
                                .header(Header.REFERER, forWebsiteCityVo.getReffer())
                                .body(json.toString()).execute().body();
                        System.out.println("result====:" + result.toString());

                        if (result.equals("Error request, response status: 577")) {
                            if (m != 1) {
                                m = websiteDTO.getPage() - 1;
                            }
                        } else {
                            JSONObject resultJsonObject = JSONObject.parseObject(result);
                            Map<String, Object> map = (Map<String, Object>) resultJsonObject;
                            if (map.get("totalPages").toString() != null && !"".equals(map.get("totalPages").toString())) {//成功查询数据
                                pageNum = Integer.parseInt(map.get("totalPages").toString());
                                JSONArray array = JSONArray.parseArray(map.get("content").toString());
                                if ("即刻订舱产品".equals(websiteDTO.getTypeName())) {
                                    for (int i = 0; i < array.size(); i++) {
                                        JSONObject jsonObject2 = (JSONObject) array.get(i);
                                        WebsiteVo websiteVo = forData(jsonObject2, websiteDTO);
                                        if(websiteVo!=null){
                                            websiteVoList.add(websiteVo);
                                        }
                                    }
                                } else if ("预定舱产品".equals(websiteDTO.getTypeName())) {
                                    //循环产品
                                    for (int i = 0; i < array.size(); i++) {
                                        JSONObject jsonObject2 = (JSONObject) array.get(i);
                                        Map<String, Object> map2 = (Map<String, Object>) jsonObject2;
                                        String groupResult = getGoodDetail(map2,token);
                                        if(groupResult!=null){
                                            JSONArray groupArray = JSONArray.parseArray(groupResult);
                                            //循环商品
                                            for (int j = 0; j < groupArray.size(); j++) {
                                                JSONObject jsonObject3 = (JSONObject) groupArray.get(j);
                                                WebsiteVo websiteVo = forData(jsonObject3, websiteDTO);
                                                if(websiteVo!=null){
                                                    websiteVoList.add(websiteVo);
                                                }
                                            }
                                        }else{
                                            throw new RuntimeException("接口请求异常");
                                        }
                                    }
                                } else if ("冷藏冷箱".equals(websiteDTO.getTypeName())) {
                                    for (int i = 0; i < array.size(); i++) {
                                        JSONObject jsonObject2 = (JSONObject) array.get(i);
                                        WebsiteVo websiteVo = forData(jsonObject2, websiteDTO);
                                        if(websiteVo!=null){
                                            websiteVoList.add(websiteVo);
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        System.out.println("websiteVoList：" + websiteVoList.toString());

        //导出并发送
        exportSendEmail(websiteVoList);
    }

    public String getGoodDetail(Map<String, Object> map2,String token) throws InterruptedException {
        //一个产品下可能会有多个商品，获取多个商品信息
        String id = null;
        if(map2.get("id")!=null && !"".equals(map2.get("id")) && map2.get("id").toString().trim().length() > 0){
            id = map2.get("id").toString();
        }else{
            throw new RuntimeException("未获取到产品ID");
        }
        String porCityId = null;
        if(map2.get("porCity")!=null && !"".equals(map2.get("porCity")) && map2.get("porCity").toString().trim().length() > 0){
            porCityId = JSONObject.parseObject(map2.get("porCity").toString()).get("id").toString();
        }else{
            throw new RuntimeException("未获取到必传参数");
        }
        String polPortId = null;
        if(map2.get("polPort")!=null && !"".equals(map2.get("polPort")) && map2.get("polPort").toString().trim().length() > 0){
            polPortId = JSONObject.parseObject(map2.get("polPort").toString()).get("id").toString();
        }else{
            throw new RuntimeException("未获取到必传参数");
        }
        String podPortId = null;
        if(map2.get("podPort")!=null && !"".equals(map2.get("podPort")) && map2.get("podPort").toString().trim().length() > 0){
            podPortId = JSONObject.parseObject(map2.get("podPort").toString()).get("id").toString();
        }else{
            throw new RuntimeException("未获取到必传参数");
        }
        String lastPodPortId = null;
        if(map2.get("lastPodPort")!=null && !"".equals(map2.get("lastPodPort")) && map2.get("lastPodPort").toString().trim().length() > 0){
            lastPodPortId = JSONObject.parseObject(map2.get("lastPodPort").toString()).get("id").toString();
        }else{
            throw new RuntimeException("未获取到必传参数");
        }
        String fndCityId = null;
        if(map2.get("fndCity")!=null && !"".equals(map2.get("fndCity")) && map2.get("fndCity").toString().trim().length() > 0){
            fndCityId = JSONObject.parseObject(map2.get("fndCity").toString()).get("id").toString();
        }else{
            throw new RuntimeException("未获取到必传参数");
        }
        String serviceCode = null;
        if(map2.get("serviceCode")!=null && !"".equals(map2.get("serviceCode")) && map2.get("serviceCode").toString().trim().length() > 0){
            serviceCode = map2.get("serviceCode").toString();
        }else{
            throw new RuntimeException("未获取到必传参数");
        }
        String effectiveStartDate = null;
        if(map2.get("effectiveStartDate")!=null && !"".equals(map2.get("effectiveStartDate")) && map2.get("effectiveStartDate").toString().trim().length() > 0){
            effectiveStartDate = map2.get("effectiveStartDate").toString();
        }else{
            throw new RuntimeException("未获取到必传参数");
        }
        String effectiveEndDate = null;
        if(map2.get("effectiveStartDate")!=null && !"".equals(map2.get("effectiveEndDate")) && map2.get("effectiveEndDate").toString().trim().length() > 0){
            effectiveEndDate = map2.get("effectiveEndDate").toString();
        }else{
            throw new RuntimeException("未获取到必传参数");
        }
        String haulageType = null;
        if(map2.get("haulageType")!=null && !"".equals(map2.get("haulageType")) && map2.get("haulageType").toString().trim().length() > 0){
            haulageType = map2.get("haulageType").toString();
        }else{
            throw new RuntimeException("未获取到必传参数");
        }
        String porFacilityCode = null;
        if(map2.get("porFacilityCode")!=null && !"".equals(map2.get("porFacilityCode")) && map2.get("porFacilityCode").toString().trim().length() > 0){
            porFacilityCode = map2.get("porFacilityCode").toString();
        }else{
            throw new RuntimeException("未获取到必传参数");
        }
        String fndFacilityCode = null;
        if(map2.get("fndFacilityCode")!=null && !"".equals(map2.get("fndFacilityCode")) && map2.get("fndFacilityCode").toString().trim().length() > 0){
            fndFacilityCode = map2.get("fndFacilityCode").toString();
        }else{
            throw new RuntimeException("未获取到必传参数");
        }
        String tradeAreaCode = null;
        if(map2.get("tradeAreaCode")!=null && !"".equals(map2.get("tradeAreaCode")) && map2.get("tradeAreaCode").toString().trim().length() > 0){
            tradeAreaCode = map2.get("tradeAreaCode").toString();
        }else{
            throw new RuntimeException("未获取到必传参数");
        }
        JSONObject jsonObject = JSONObject.parseObject("{\"id\":\""+id+"\",\"porCity\":{\"id\":\""+porCityId+"\"},\"polPort\":{\"id\":\""+polPortId+"\"}," +
                "\"podPort\":{\"id\":\""+podPortId+"\"},\"lastPodPort\":{\"id\":\""+lastPodPortId+"\"},\"fndCity\":{\"id\":\""+fndCityId+"\"},," +
                "\"serviceCode\":\""+serviceCode+"\",\"effectiveStartDate\":\""+effectiveStartDate+"\",\"effectiveEndDate\":\""+effectiveEndDate+"\"," +
                "\"haulageType\":\""+haulageType+"\",\"porFacilityCode\":\""+porFacilityCode+"\",\"fndFacilityCode\":\""+fndFacilityCode+"\"," +
                "\"tradeAreaCode\":\""+tradeAreaCode+"\",\"isFmcProduct\":false}");
        String groupResult = null;
        for (int i = 0; i < 10; i++) {
            Thread.sleep(500);
            groupResult = getQueryData(jsonObject,token);
            System.out.println("groupResult:"+groupResult);
            if("[]".equals(groupResult)){
                return null;
            }
            if(!groupResult.contains("[{\"id\"")){
                if (groupResult.equals("Error request, response status: 577")) {
                    if (i != 0) {
                        i = i - 1;
                    }
                    continue;
                }else{
                    return null;
                }
            }
            break;
        }
        return groupResult;
    }

    public String getQueryData(JSONObject jsonObject,String token){
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String groupUrl = "https://synconhub.coscoshipping.com/api/product/client/prebooking/product/list";
        String groupResult = HttpRequest.post(groupUrl)
                .header(Header.ACCEPT, "application/json, text/plain, */*")
                .header(Header.ACCEPT_ENCODING, "gzip, deflate, br")
                .header(Header.ACCEPT_LANGUAGE, "zh-CN,zh;q=0.9")
                .header(Header.CONNECTION, "keep-alive")
                .header(Header.CONTENT_LENGTH, "4243")
                .header(Header.HOST, "synconhub.coscoshipping.com")
                .header(Header.COOKIE, "HMF_CI=1f8ac55586176f5080ec9b630951356268c94a36660c08e26ebcc8c13f03d96752; CSH_DF=1ClHD9LpVa84OwZAh5MOUdlqMdXnkb2KAofAIwHYnfU3s2rZMw+UYawPG5UjcjoVQ3; cookiePolicy=Accepted; cookiePreference=Accepted; Hm_lvt_d16e4336470e6b4d88aa16ff927d7daf=1633936871; Hm_lpvt_d16e4336470e6b4d88aa16ff927d7daf=1633936871; token=" + token + "; django_language=zh-cn; sessionid=vugf5y10vfydigyl85twzd706m9atk0v")
                .header("X-Auth-Token", token)
                .header(Header.CONTENT_TYPE, "application/json;charset=UTF-8")
                .header(Header.REFERER, "https://synconhub.coscoshipping.com/prebooking?channelCode=GENERAL&prodType=P&startDate="+sdf.format(new Date())+"%2000%3A00&cargoCategory=GENERAL&porCityName=&fndCityName=&page=1")
                .body(jsonObject.toString()).execute().body();
        return groupResult;
    }

    //获取产品信息
    public WebsiteVo forData(JSONObject jsonObject2, WebsiteDTO websiteDTO) {
        Map<String, Object> map2 = (Map<String, Object>) jsonObject2;
        String goodId = map2.get("id").toString();//商品id
        if (goodId != null && goodId.trim().length() > 0) {
            if (Integer.parseInt(map2.get("inventory").toString()) > 0) {
                WebsiteVo websiteVo = new WebsiteVo();
                websiteVo.setId(goodId);//商品id
                websiteVo.setProductType(websiteDTO.getTypeName());//商品类型
                if (websiteDTO.getTypeName().equals("远期产品")) {
                    websiteVo.setEstimatedTransitTimeInDays(0);//航程天数
                    websiteVo.setVesselName("-");//航线名称
                } else {
                    websiteVo.setEstimatedTransitTimeInDays(Integer.parseInt(map2.get("estimatedTransitTimeInDays").toString()));//航程天数
                    websiteVo.setVesselName(map2.get("vesselName").toString());//航线名称
                }
                websiteVo.setEffectiveStartDate(map2.get("effectiveStartDate").toString().substring(0, 10));
                websiteVo.setEffectiveEndDate(map2.get("effectiveEndDate").toString().substring(0, 10));
                JSONObject porCity = new JSONObject();
                porCity = JSONObject.parseObject(map2.get("porCity").toString());
                websiteVo.setPorCityId(porCity.get("id").toString());
                websiteVo.setPorCityFullNameCn(porCity.get("cityFullNameCn").toString());
                websiteVo.setPorCityFullNameEn(porCity.get("cityFullNameEn").toString());
                if (map2.get("porFacilityCode") != null) {
                    websiteVo.setPor(map2.get("porFacilityCode").toString());
                } else {
                    websiteVo.setPor("");
                }
                JSONObject polPort = new JSONObject();
                polPort = JSONObject.parseObject(map2.get("polPort").toString());
                websiteVo.setEtdCityCh(polPort.get("portFullNameCn").toString());
                websiteVo.setEtdCityEng(polPort.get("portFullNameEn").toString());
                JSONObject podPort = new JSONObject();
                podPort = JSONObject.parseObject(map2.get("podPort").toString());
                websiteVo.setEtaCityCh(podPort.get("portFullNameCn").toString());
                websiteVo.setEtaCityEng(podPort.get("portFullNameEn").toString());
                JSONObject fndCity = new JSONObject();
                fndCity = JSONObject.parseObject(map2.get("fndCity").toString());
                websiteVo.setFndCityId(fndCity.get("id").toString());
                websiteVo.setFndCityFullNameCn(fndCity.get("cityFullNameCn").toString());
                websiteVo.setFndCityFullNameEn(fndCity.get("cityFullNameEn").toString());
                if (map2.get("fndFacilityCode") != null) {
                    websiteVo.setDelivery(map2.get("fndFacilityCode").toString());
                } else {
                    websiteVo.setDelivery("");
                }
                //商品规格
                JSONArray jsonArrayRouteProductPricingList = JSONArray.parseArray(map2.get("routeProductPricingList").toString());
                List<RouteProductPricingVo> routeProductPricingList = new ArrayList<RouteProductPricingVo>();
                websiteVo.setTwentyGPPrice("0");
                websiteVo.setFortyGPPrice("0");
                websiteVo.setFortyHQPrice("0");
                websiteVo.setTwentyRFPrice("0");
                websiteVo.setFortyRQPrice("0");
                for (int j = 0; j < jsonArrayRouteProductPricingList.size(); j++) {
                    JSONObject jsonObject = jsonArrayRouteProductPricingList.getJSONObject(j);
                    if ("20GP".equals(jsonObject.get("cntrType"))) {
                        if (jsonObject.get("tradePrice") != null) {
                            websiteVo.setTwentyGPPrice(jsonObject.get("tradePrice").toString());
                        } else {
                            websiteVo.setTwentyGPPrice(jsonObject.get("price").toString());
                        }
                    } else if ("40GP".equals(jsonObject.get("cntrType"))) {
                        if (jsonObject.get("tradePrice") != null) {
                            websiteVo.setFortyGPPrice(jsonObject.get("tradePrice").toString());
                        } else {
                            websiteVo.setFortyGPPrice(jsonObject.get("price").toString());
                        }
                    } else if ("40HQ".equals(jsonObject.get("cntrType"))) {
                        if (jsonObject.get("tradePrice") != null) {
                            websiteVo.setFortyHQPrice(jsonObject.get("tradePrice").toString());
                        } else {
                            websiteVo.setFortyHQPrice(jsonObject.get("price").toString());
                        }
                    } else if ("20RF".equals(jsonObject.get("cntrType"))) {
                        if (jsonObject.get("tradePrice") != null) {
                            websiteVo.setTwentyRFPrice(jsonObject.get("tradePrice").toString());
                        } else {
                            websiteVo.setTwentyRFPrice(jsonObject.get("price").toString());
                        }
                    } else if ("40RQ".equals(jsonObject.get("cntrType"))) {
                        if (jsonObject.get("tradePrice") != null) {
                            websiteVo.setFortyRQPrice(jsonObject.get("tradePrice").toString());
                        } else {
                            websiteVo.setFortyRQPrice(jsonObject.get("price").toString());
                        }
                    }

                    RouteProductPricingVo routeProductPricingVo = new RouteProductPricingVo();
                    routeProductPricingVo.setCurrency(jsonObject.get("cntrType").toString());
                    routeProductPricingVo.setPrice(new BigDecimal(jsonObject.get("price").toString()));
                    if (jsonObject.get("tradePrice") != null) {
                        routeProductPricingVo.setTradePrice(new BigDecimal(jsonObject.get("tradePrice").toString()));
                    } else {
                        routeProductPricingVo.setTradePrice(new BigDecimal(jsonObject.get("price").toString()));
                    }
                    routeProductPricingVo.setCntrType(jsonObject.get("currency").toString());
                    routeProductPricingList.add(routeProductPricingVo);
                }
                websiteVo.setRouteProductPricingList(routeProductPricingList);
                if (map2.get("promotions") != null) {
                    //优惠promotions
                    JSONObject jsonObject = new JSONObject();
                    jsonObject = JSONObject.parseObject(map2.get("promotions").toString());
                    PromotionsVo promotionsVo = new PromotionsVo();
                    if (jsonObject.get("detail").toString().trim().length() > 0) {
                        JSONArray detailJsonArray = JSONArray.parseArray(jsonObject.get("detail").toString());
                        List<DetailVo> detailList = new ArrayList<DetailVo>();
                        for (int j = 0; j < detailJsonArray.size(); j++) {
                            DetailVo detailVo = new DetailVo();
                            JSONObject jo = detailJsonArray.getJSONObject(j);
                            detailVo.setPromotionType(jo.get("promotionType").toString());
                            detailVo.setRemnantInventory(new BigDecimal(jo.get("remnantInventory").toString()));
                            detailVo.setSummary(jo.get("summary").toString());
                            detailVo.setPriority(new BigDecimal(jo.get("priority").toString()));
                            detailList.add(detailVo);
                        }
                        promotionsVo.setDetailList(detailList);
                    }
                    List<PromotionsVo> promotionsVoList = new ArrayList<PromotionsVo>();
                    promotionsVoList.add(promotionsVo);
                    websiteVo.setPromotionsVoList(promotionsVoList);
                }
                if (map2.get("companyDiscountsInfoDTO") != null) {
                    //优惠companyDiscountsInfoDTO
                    JSONArray jsonArrayCompanyDiscountsInfoList = JSONArray.parseArray(map2.get("companyDiscountsInfoDTO").toString());
                    List<CompanyDiscountsInfoVo> companyDiscountsInfoList = new ArrayList<CompanyDiscountsInfoVo>();
                    for (int j = 0; j < jsonArrayCompanyDiscountsInfoList.size(); j++) {
                        CompanyDiscountsInfoVo companyDiscountsInfoVo = new CompanyDiscountsInfoVo();
                        JSONObject jsonObjectCompanyDiscountsInfoVo = jsonArrayCompanyDiscountsInfoList.getJSONObject(j);
                        companyDiscountsInfoVo.setChannelCntr(jsonObjectCompanyDiscountsInfoVo.get("channelCntr").toString());
                        companyDiscountsInfoVo.setAmount(new BigDecimal(jsonObjectCompanyDiscountsInfoVo.get("amount").toString()));
                        companyDiscountsInfoVo.setCurrencyType(jsonObjectCompanyDiscountsInfoVo.get("currencyType").toString());
                        companyDiscountsInfoList.add(companyDiscountsInfoVo);
                    }
                    websiteVo.setCompanyDiscountsInfoList(companyDiscountsInfoList);
                }
                websiteVo.setInventoryCount(Integer.parseInt(map2.get("inventory").toString()));//库存数量
                websiteVo.setTerms(map2.get("haulageType").toString());//条款
                if (map2.get("etd") == null) {
                    websiteVo.setEtdDate("");
                } else {
                    websiteVo.setEtdDate(map2.get("etd").toString());
                }
                if (map2.get("eta") == null) {
                    websiteVo.setEtaDate("");
                } else {
                    websiteVo.setEtaDate(map2.get("eta").toString());
                }
                //库存大于0
                if (websiteVo.getInventoryCount() > 0) {
                    return websiteVo;
                }
            }
        }
        return null;
    }

    //导出并发送
    public String exportSendEmail(List<WebsiteVo> websiteVoList) throws Exception {
        //表的标题名称
        String sheetName = "捷信通达SOC监管数据统计表";
        //表头集合
        List<String> tableHead = new ArrayList<String>();
        tableHead.add("商品类型");
        tableHead.add("POR");
        tableHead.add("POR CH");
        tableHead.add("POR ENG");
        tableHead.add("POL CH");
        tableHead.add("POL ENG");
        tableHead.add("POD CH");
        tableHead.add("POD ENG");
        tableHead.add("DEILVERY");
        tableHead.add("DEILVERY CH");
        tableHead.add("DEILVERY ENG");
        tableHead.add("date of deliver");
        tableHead.add("ETD");
        tableHead.add("ETA");
        tableHead.add("VESSEL/VOY");
        tableHead.add("Terms");
        tableHead.add("20GP");
        tableHead.add("40GP");
        tableHead.add("40HC");
        tableHead.add("20RF");
        tableHead.add("40RH");
        tableHead.add("STOCK");
        tableHead.add("VALID TO END");
        //表格内数据内容，此处为一个双层集合数组，外层数组代表的是总的表格内容，数组长度为行数（不包括标题和表头），
        //第二层数组类似于行，数组内每个元素对应一个单元格，因此二层数组长度应与表头数组长度一致，不然会出现单元格缺失
        List<List<Object>> tableBody = new ArrayList<>();

        for (WebsiteVo forWebsiteVo : websiteVoList) {
            List<Object> tableRow = new ArrayList<>();
            tableRow.add(forWebsiteVo.getProductType());
            tableRow.add(forWebsiteVo.getPor());
            tableRow.add(forWebsiteVo.getPorCityFullNameCn());
            tableRow.add(forWebsiteVo.getPorCityFullNameEn());
            tableRow.add(forWebsiteVo.getEtdCityCh());
            tableRow.add(forWebsiteVo.getEtdCityEng());
            tableRow.add(forWebsiteVo.getEtaCityCh());
            tableRow.add(forWebsiteVo.getEtaCityEng());
            tableRow.add(forWebsiteVo.getDelivery());
            tableRow.add(forWebsiteVo.getFndCityFullNameCn());
            tableRow.add(forWebsiteVo.getFndCityFullNameEn());
            tableRow.add(forWebsiteVo.getEstimatedTransitTimeInDays());
            tableRow.add(forWebsiteVo.getEtdDate());
            tableRow.add(forWebsiteVo.getEtaDate());
            tableRow.add(forWebsiteVo.getVesselName());
            tableRow.add(forWebsiteVo.getTerms());
            tableRow.add(forWebsiteVo.getTwentyGPPrice());
            tableRow.add(forWebsiteVo.getFortyGPPrice());
            tableRow.add(forWebsiteVo.getFortyHQPrice());
            tableRow.add(forWebsiteVo.getTwentyRFPrice());
            tableRow.add(forWebsiteVo.getFortyRQPrice());
            tableRow.add(forWebsiteVo.getInventoryCount());
            tableRow.add(forWebsiteVo.getEffectiveStartDate() + " 至 " + forWebsiteVo.getEffectiveEndDate());
            tableBody.add(tableRow);
        }

        boolean boo = false;
        int num = 0;
        //此处为多邮箱接收，直接在此处add多个邮箱即可
        List<String> stringList = new ArrayList<>();
//        stringList.add("jhywjh@163.com");//姜洪源
        stringList.add("18253236955@163.com");//徐峥
        for (String str : stringList) {
            try {
                InputStream is = exportXls(sheetName, tableHead, tableBody);
                if (is == null) {
                    return "创建表格失败";
                } else {
                    boo = sendMail(is, sheetName, str);
                    if (boo) {
                        num++;
                    }
                    is.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        if (num == stringList.size()) {
            return "导出成功";
        } else {
            return "导出数据失败";
        }
    }

    /**
     * 生成表格并返回结果流
     *
     * @param sheetName 表名
     * @param tableHead 表头
     * @param tableBody 表格数据（双层集合数组）
     * @return 生成的表格以流的形式返回
     * @throws Exception
     */
    public InputStream exportXls(String sheetName, List<String> tableHead, List<List<Object>> tableBody)
            throws Exception {
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 创建一张excel表
        HSSFSheet sheet = workbook.createSheet(sheetName);
        //   ---------------- 表标题样式 -------------------
        HSSFFont headfont = workbook.createFont();
        headfont.setFontName("宋体");
        // 设置字体大小
        headfont.setFontHeightInPoints((short) 18);
        //粗体显示
        headfont.setBold(true);
        //设置单元格样式
        HSSFCellStyle headstyle = workbook.createCellStyle();
        //设置文字样式
        headstyle.setFont(headfont);
        // 单元格内容左右居中
        // 设置单元格上下居中
        headstyle.setAlignment(HorizontalAlignment.CENTER);
        headstyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置标题固定
        headstyle.setLocked(true);

//   ---------------  表头样式 -----------------
        HSSFFont font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 12);
        HSSFCellStyle style = workbook.createCellStyle();
        //设置表头背景色
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
        style.setFillBackgroundColor(IndexedColors.GREY_25_PERCENT.index);
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //单元格边框属性设置
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setLocked(true);

//   -------------- 普通单元格样式（中文） ---------------
        HSSFFont font2 = workbook.createFont();
        font2.setFontName("宋体");
        font2.setFontHeightInPoints((short) 12);
        font2.setBold(true);
        HSSFCellStyle style2 = workbook.createCellStyle();
        style2.setBorderBottom(BorderStyle.THIN);
        style2.setBorderLeft(BorderStyle.THIN);
        style2.setBorderRight(BorderStyle.THIN);
        style2.setBorderTop(BorderStyle.THIN);
        style2.setFont(font2);
        // 设置单元格内容自动换行
        style2.setWrapText(true);
        style2.setAlignment(HorizontalAlignment.CENTER);
        style2.setVerticalAlignment(VerticalAlignment.CENTER);

        //设置默认行高
        sheet.setDefaultRowHeight((short) 360);
        // 第一行，添加表的标题
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, tableHead.size() - 1));
        HSSFRow row = sheet.createRow(0);
        row.setHeight((short) 0x349);
        HSSFCell cell = row.createCell(0);
        cell.setCellStyle(headstyle);
        CellUtil.setCellValue(cell, sheetName);

        //第二行，添加表头
        row = sheet.createRow(1);
        row.setHeight((short) 0x180);
        for (int j = 0; j < tableHead.size(); j++) {
            //设置每列的宽度 （自定义列宽） 4200为每列最小宽度
            int dataLength = tableHead.get(j).getBytes().length * 2 * 232;
            sheet.setColumnWidth((short) j, dataLength < 4200 ? 4200 : dataLength);
            //在行内创建一个单元格
            cell = row.createCell(j);
            //单元格设置自定样式 style（上边有定义）
            cell.setCellStyle(style);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            //将数据填充进表格内 cell：创建的单元格    tableHead.get(j):数据
            CellUtil.setCellValue(cell, tableHead.get(j));
        }
        // 设置列值-每行数据的内容
        for (int i = 0; i < tableBody.size(); i++) {
            //标题、表头字段共占了2行，所以在填充数据的时候要加2，也就是数据要从第3行开始填充
            row = sheet.createRow(i + 2);
            for (int j = 0; j < tableBody.get(i).size(); j++) {
                cell = row.createCell(j);
                cell.setCellStyle(style2);
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                CellUtil.setCellValue(cell, tableBody.get(i).get(j));
            }
        }
        //如果你需要将excel放置到某个地方，比如说写出到某个文件夹下，那可以在直接在此处进行写出即可，根据自己业务做修改。这里只是将excel文件以流的形式return，用于其他方法做操作。
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        workbook.write(bos);
        return new ByteArrayInputStream(bos.toByteArray());
    }

    /**
     * 发送邮件
     *
     * @param is                          表格输出流
     * @param fileName                    发送的文件名称 (例：xxxx统计表)
     * @param reUserMail：12345678@qq.com）
     */
    public boolean sendMail(InputStream is, String fileName, String reUserMail) {
        Transport transport = null;
        try {
            System.setProperty("mail.mime.splitlongparameters", "false");
            Properties props = new Properties();
            // 设置发送邮件的邮件服务器的属性（这里使用网易的smtp服务器）
            props.put("mail.smtp.host", "smtp.163.com");
            // 需要经过授权，也就是有户名和密码的校验，这样才能通过验证（一定要有这一条）
            props.put("mail.smtp.auth", "true");
            // 用刚刚设置好的props对象构建一个session
            Session session = Session.getDefaultInstance(props);
            // 有了这句便可以在发送邮件的过程中在console处显示过程信息，供调试使
            // 用（你可以在控制台（console)上看到发送邮件的过程）
            session.setDebug(false);
            // 用session为参数定义消息对象
            MimeMessage message = new MimeMessage(session);
            // 加载发件人地址
            message.setFrom(new InternetAddress("18253236955@163.com"/*删除此段-发件邮箱*/));
            // 加载收件人地址
            message.addRecipient(Message.RecipientType.TO, new InternetAddress(reUserMail));
            // 加载标题
            message.setSubject(fileName);
            // 向multipart对象中添加邮件的各个部分内容，包括文本内容和附件
            Multipart multipart = new MimeMultipart();
            // 设置邮件的文本内容
            BodyPart contentPart = new MimeBodyPart();
            contentPart.setText("请查看捷信通达SOC监管数据统计表附件");
            multipart.addBodyPart(contentPart);
            // 添加附件
            BodyPart messageBodyPart = new MimeBodyPart();
            DataSource source = new ByteArrayDataSource(is, "application/msexcel");
            // 添加附件的内容
            messageBodyPart.setDataHandler(new DataHandler(source));
            // 添加附件的标题
            // 这里很重要，通过下面的Base64编码的转换可以保证你的中文附件标题名在发送时不会变成乱码
            messageBodyPart.setFileName(MimeUtility.encodeText(fileName + ".xls"));
            multipart.addBodyPart(messageBodyPart);
            // 将multipart对象放到message中
            message.setContent(multipart);
            // 保存邮件
            message.saveChanges();
            // 发送邮件
            transport = session.getTransport("smtp");
            // 连接服务器的邮箱
            transport.connect("smtp.163.com", "18253236955@163.com"/*删除此段-发件人名称*/, "xz931120"/*删除此段-发件人邮箱密码*/);
            // 把邮件发送出去
            transport.sendMessage(message, message.getAllRecipients());
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            try {
                transport.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}
